"""
    其他容器处理：
        1.列表，字典，集合

        2.数据库

        3.excel表格
            1. 双击打开

            2. 选中其中一个选项卡

            3. 选中其中一个表格进行读取或者编辑

            4.保存，退出
            xlrd : 专门读取excel表格 ：0.9.3版本
            xlwt : 专门写入excel表格
"""
import xlrd

mobile_network = [
                  ['134', '135', '136', '137', '138',
                   '139', '147', '150', '151', '152',
                   '157', '158', '159', '178', '182',
                   '183', '184', '187', '188'],
                  ['1703', '1705', '1706']
]

unicom_network = [
                  ['130', '131', '132', '145', '155',
                   '156', '171', '175', '176', '185',
                   '186'],
                  ['1704', '1707', '1708', '1709']
]

tele_network = [
                  ['133', '149', '153', '173', '177',
                   '180', '181', '189'],
                  ['1700', '1701', '1702']
]

# 打开工作簿
book = xlrd.open_workbook(filename=r"D:\工作\2023年6月\5.baidu-员工的人员信息.xls")


# 2.选择其中一个选项卡
st = book.sheet_by_index(0)


# 3.读取其中一个表格
# data = st.cell_value(0,0)  # 通过x,y坐标获取表格数据
# data1 = st.cell_value(0,1)

# 4.获取多少行和多少列
# rows = st.nrows # 获取行数据
# cols = st.ncols # 获取列数

# print("有",rows,"行！",cols,"列！")

# data = st.row_values(1)  # rows_values(index) 通过行编号获取某行数据


# for i in data1:
#     s = s + i
# print("总销售量为：",s)


# 1.统计表格人数
data_num_peo = st.col_values(1)[1:] # 切片 # 通过列编号获取某一列数据

nset = set()
for i in data_num_peo:
    nset.add(i)

nrows = len(nset)

print('表格中共有', nrows, '人。')
print()


# 2.统计办电信，联通，移动的用户数量并计算出三种用户的占比
data_tele_num = st.col_values(5)[1:]
mob_num = 0
uni_num = 0
tel_num = 0
for i in data_tele_num:
    tele_num = i[0:3]
    if tele_num == '170':
        tele_num = i[0:4]
        if tele_num in mobile_network[1]:
            mob_num += 1
        elif tele_num in unicom_network[1]:
            uni_num += 1
        else:
            tel_num += 1
    elif tele_num in mobile_network[0]:
        mob_num += 1
    elif tele_num in unicom_network[0]:
        uni_num += 1
    else:
        tel_num += 1

total_num = mob_num + uni_num + tel_num
mob_pro = mob_num / total_num
uni_pro = uni_num / total_num
tel_pro = tel_num / total_num

print('电信的用户数量为：', tel_num, '位，用户在三大运营商中所占比例为：', tel_pro * 100, '%')
print('联通的用户数量为：', uni_num, '位，用户在三大运营商中所占比例为：', uni_pro * 100, '%')
print('移动的用户数量为：', mob_num, '位，用户在三大运营商中所占比例为：', mob_pro * 100, '%')
print()


# 3.总公司男女人数
data_sex_num = st.col_values(8)[1:]
male_num = 0
female_num = 0
for i in data_sex_num:
    if i == '男':
        male_num += 1
    else:
        female_num += 1
print('男性人数为：', male_num)
print('女性人数为：', female_num)
print()


# 4.年龄超过45岁的老员工人数
data_age = st.col_values(7)[1:]
elder_num = 0
for i in data_age:
    if i > 45:
        elder_num += 1
print('年龄超过45岁的老员工数量为：', elder_num)


# 5.薪资高于8000元的高薪人员数量和薪资低于3000的低薪人员数量
data_wage = st.col_values(11)[1:]
wellpayd_num = 0
lowwage_num = 0
for i in data_wage:
    if i > 8000:
        wellpayd_num += 1
    elif i < 3000:
        lowwage_num += 1
print('薪资高于8000元的高薪人员数量为：', wellpayd_num)
print('薪资低于3000元的低薪人员数量为：', lowwage_num)
print()


# 6.统计去传媒公司的工作的人员数量
data_company = st.col_values(13)[1:]
media_num = 0
for i in data_company:
    if '传媒' in i:
        media_num += 1

print("去传媒公司的工作的人员数量为：", media_num)
print()


# 7.统计一下可能在疫情高危地区的人数
data_adderss = st.col_values(9)[1:]
high_risk_num = 0
for i in data_adderss:
    i = i[0:3]
    if '黑龙江' in i or '北京' in i or '福建' in i or '四川' in i:
        high_risk_num += 1

print(data_adderss[0][1:3])

print('可能在疫情高危地区的人数为：', high_risk_num)